package com.dhcc.DRGsHosp.application.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.ICDApplication;
import com.dhcc.DRGsHosp.infra.util.StringUtils;

/**
 * @title		:ICDApplicationImpl.java
 * @description	:
 * @author		:lileizheng
 * @date		:2015年8月12日
 * @version     :1.0
 * @copyright   :DHCC 2013-2014 
 */
@Named("icdApplicationImplqiaoyilong20181026")
@Transactional
public class ICDApplicationImpl implements ICDApplication {
    
    private EntityRepository repository;
    
    @Autowired  
	private JdbcTemplateObj jdbcTemplateObj;
    


	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
	
	/**  
/**
 * 
 *  @Description：：ICD自动对照
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.ICDApplication#matchICD(java.lang.String, java.lang.String)
 */
	@Override
	public int matchICD(String icdVersion, String icdType) {
		if("D".equals(icdType)){
			   String sql= "MERGE INTO drgs.t_icdmatch_result_dia dia\n" +
						   "using (\n" + 
						   "select a.diag_code, a.diagnosis,\n" + 
						   "case when b.dicd_code is not null then b.dicd_code\n" + 
						   "when d.dicd_code is not null then d.dicd_code\n" + 
						   "when e.dicd_code is not null then e.dicd_code\n" + 
						   "else null end dicd_code,\n" + 
						   "case when b.dicd_code is not null then b.dicd_desc\n" + 
						   "when d.dicd_code is not null then d.dicd_desc\n" + 
						   "when e.dicd_code is not null then e.dicd_desc\n" + 
						   "else null end dicd_desc,\n" + 
						   "'"+icdVersion+"' dicd_version,\n" + 
						   "case when b.dicd_desc is not null then 0\n" + 
						   "when d.dicd_desc is not null then 2\n" + 
						   "when e.dicd_desc is not null then 3\n" + 
						   "else 4 end match_type, sysdate update_date\n" + 
						   "from (\n" + 
						   "select a.PD0101 diag_code, a.PD0100 diagnosis\n" + 
						   "from drgs.MR_DIAGNOSE a where a.PD0101 is not null and a.PD0100 is not NULL\n" + 
						   "group by a.PD0101, a.PD0100 ) a\n" + 
						   "left join drgs.t_icd_bj_dia b on a.diag_code=b.dicd_code and a.diagnosis=b.dicd_desc and b.dicd_version='"+icdVersion+"'--完全匹配,编码和描述均相同\n" + 
						   "left join drgs.t_icd_bj_dia d on a.diag_code=d.dicd_code and a.diagnosis<>d.dicd_desc and d.dicd_version='"+icdVersion+"'--不完全匹配,编码相同描述不同\n" + 
						   "left join (select t.*, row_number() over(partition by t.dicd_desc order by 1) rowIndex from drgs.t_icd_bj_dia t) e on a.diag_code<>e.dicd_code and a.diagnosis=e.dicd_desc and e.dicd_version='"+icdVersion+"' and rowIndex=1 --不完全匹配, 编码不同描述相同\n" + 
						   ")ic\n" + 
						   "on (dia.hosp_icd_code =ic.diag_code and dia.hosp_icd_desc=ic.diagnosis and dia.drg_icd_version=ic.dicd_version)\n" + 
						   "when matched THEN\n" + 
						   "update SET dia.BJ_ICD_CODE=ic.dicd_code, dia.BJ_ICD_DESC=ic.dicd_desc, dia.ICDMAT_TYPE=ic.match_type, dia.LAST_UPDATE_DATE=ic.update_date where dia.icdmat_type not in ('1','0')\n" + 
						   "when not matched then\n" + 
						   "insert (dia.hosp_icd_code, dia.bj_icd_code, dia.icdmat_type, dia.last_update_date, dia.hosp_icd_desc, dia.bj_icd_desc, dia.drg_icd_version) values(ic.diag_code,ic.dicd_code,ic.match_type,ic.update_date,ic.diagnosis,ic.dicd_desc, ic.dicd_version)";
			  return jdbcTemplateObj.update(sql);
		}else {
			String sql= "MERGE INTO drgs.t_icdmatch_result_oper oper\n" +
						"using (\n" + 
						"select a.oper_code, a.operation,\n" + 
						"case when b.oicd_code is not null then b.oicd_code\n" + 
						"when d.oicd_code is not null then d.oicd_code\n" + 
						"when e.oicd_code is not null then e.oicd_code\n" + 
						"else null end oicd_code,\n" + 
						"case when b.oicd_code is not null then b.oicd_desc\n" + 
						"when d.oicd_code is not null then d.oicd_desc\n" + 
						"when e.oicd_code is not null then e.oicd_desc\n" + 
						"else null end oicd_desc,\n" + 
						"case when b.oicd_desc is not null  then 0\n" + 
						"when d.oicd_desc is not null then 2  when e.oicd_desc is not null then 3\n" + 
						"else 4 end match_type, sysdate update_date,\n" + 
						"case when b.oicd_code is not null then b.is_oper\n" + 
						"when d.oicd_code is not null then d.is_oper\n" + 
						"when e.oicd_code is not null then e.is_oper\n" + 
						"else null end is_oper,\n" + 
						"'"+icdVersion+"' drg_icd_version\n" + 
						"from (\n" + 
						"select  a.PO0101 oper_code, a.PO0100 operation\n" + 
						"from drgs.MR_OPERATION a\n" + 
						"where a.PO0101 is not null and a.PO0100 is not NULL\n" + 
						"group by a.PO0101, a.PO0100) a\n" + 
						"left join drgs.t_icd_bj_oper b on a.oper_code=b.oicd_code and a.operation=b.oicd_desc and b.oicd_version='"+icdVersion+"'\n" + 
						"left join drgs.t_icd_bj_oper d on a.oper_code=d.oicd_code and a.operation<>d.oicd_desc and d.oicd_version='"+icdVersion+"'\n" + 
						"left join (select t.*, row_number() over(partition by t.oicd_desc order by 1) rowIndex from drgs.t_icd_bj_oper t) e on a.oper_code<>e.oicd_code and a.operation=e.oicd_desc and e.oicd_version='"+icdVersion+"' and rowIndex=1\n" + 
						")ic\n" + 
						"on (oper.hosp_icd_code =ic.oper_code and oper.hosp_icd_desc=ic.operation and oper.drg_icd_version=ic.drg_icd_version)\n" + 
						"when matched THEN\n" + 
						"update SET oper.BJ_ICD_CODE=ic.oicd_code, oper.BJ_ICD_DESC=ic.oicd_desc,oper.ICDMAT_TYPE=ic.match_type, oper.LAST_UPDATE_DATE=ic.update_date,oper.is_oper=ic.is_oper  where oper.icdmat_type not in('1','0')\n" + 
						"when not matched then\n" + 
						"insert (oper.hosp_icd_code,oper.bj_icd_code ,oper.icdmat_type,oper.last_update_date,oper.hosp_icd_desc,oper.bj_icd_desc,oper.is_oper,oper.drg_icd_version) values(ic.oper_code,ic.oicd_code,ic.match_type,ic.update_date,ic.operation,ic.oicd_desc,ic.is_oper,ic.drg_icd_version)\n";
			int count=jdbcTemplateObj.update(sql);
			updateOperation(icdVersion);
			return count;
		}
	}

	/**
	 * 根据传入的相关信息，返回对应的ICD对照结果
	 */
	@Override
	public List<Map<String,Object>> getICDMatching(Map<String, Object> param){
		String icdType = param.get("icdType")!=null?param.get("icdType").toString():null;
		String matchType = param.get("matchType")!=null?param.get("matchType").toString():null;
		String depCode = param.get("depCode")!=null?param.get("depCode").toString():null;
		String icdVersion = param.get("icdVersion")!=null?param.get("icdVersion").toString():null;
		String tableName="";
    	StringBuilder sql = new StringBuilder();
    	if("O".equals(icdType)){
    		tableName="drgs.t_icdmatch_result_oper";
    	}else{
    		tableName="drgs.t_icdmatch_result_dia";
    	}
    	sql.append(" select a.hosp_icd_code hICDCode, a.hosp_icd_desc hICDDesc,a.bj_icd_code bjICDCode, \n ");
    	sql.append(" a.bj_icd_desc bjICDDesc,to_char(last_update_date, 'yyyy-mm-dd hh24:mi') lastUpdateDate \n");
    	sql.append(" from "+tableName+" a \n ");
    	sql.append(" where 1=1");
    	if(StringUtils.hasLength(icdVersion)){
    		sql.append(" and a.drg_icd_version='"+icdVersion+"' \n ");
    	}
    	if(StringUtils.hasLength(depCode)){
    		if("O".equals(icdType)){
    			sql.append(" and exists( \n ");
            	sql.append(" select 1 from drgs.MR_OPERATION b \n ");
            	sql.append(" left join drgs.MR_IPINFO c \n ");
            	sql.append(" on b.PMAININDEX=c.PMAININDEX \n ");
            	sql.append(" where b.PO0101 is not null and b.PO0100 is not null \n ");
        		sql.append(" and c.P00300001='"+depCode+"' \n ");
        		sql.append(" and b.PO0101=a.hosp_icd_code and b.PO0100=a.hosp_icd_desc \n ");
            	sql.append(" ) \n ");
    		}else{
    			sql.append(" and exists( \n ");
            	sql.append(" select 1 from drgs.MR_DIAGNOSE b \n ");
            	sql.append(" left join drgs.MR_IPINFO c \n ");
            	sql.append(" on b.PMAININDEX=c.PMAININDEX \n ");
            	sql.append(" where b.PD0101 is not null and b.PD0100 is not null \n ");
        		sql.append(" and c.P00300001='"+depCode+"' \n ");
        		sql.append(" and b.PD0101=a.hosp_icd_code and b.PD0100=a.hosp_icd_desc \n ");
            	sql.append(" ) \n ");
    		}
    	}
    	if(StringUtils.hasLength(matchType)){
    		sql.append(" and a.icdmat_type='"+matchType+"' \n ");
    	}
    	sql.append(" order by a.last_update_date desc \n ");
    	List<Map<String,Object>>  resultList=jdbcTemplateObj.queryForList(sql.toString());
    	return resultList;
    }
	
/**
 * 
 *  @Description：获取icd对照匹配结果
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.ICDApplication#getICDMatchResult(java.lang.String, java.lang.String)
 */
	@Override
	public List<Object[]> getICDMatchResult(String icdType, String depCode, String icdVersion) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select  \n ");
		sql.append(" count(1) t_total, \n ");
		sql.append(" sum(decode(icdmat_type,0,1,1,1,0)) match01, \n ");
		sql.append(" sum(decode(icdmat_type,2,1,0)) match2, \n ");
		sql.append(" sum(decode(icdmat_type,3,1,0)) match3, \n ");
		sql.append(" sum(decode(icdmat_type,4,1,0)) match4 \n ");
		if("O".equals(icdType)){
			sql.append(" from drgs.t_icdmatch_result_oper a \n ");
		}else{
			sql.append(" from drgs.t_icdmatch_result_dia a \n ");
		}
		sql.append("where 1=1 \n");
		if(StringUtils.hasLength(icdVersion)){
			sql.append(" and a.drg_icd_version='"+icdVersion+"' \n ");
		}
    	if(StringUtils.hasLength(depCode)){
    		if("O".equals(icdType)){
    			sql.append(" and exists( \n ");
            	sql.append(" select 1 from drgs.MR_OPERATION b \n ");
            	sql.append(" left join drgs.MR_IPINFO c \n ");
            	sql.append(" on b.PMAININDEX=c.PMAININDEX \n ");
            	sql.append(" where b.PO0101 is not null and b.PO0100 is not null \n ");
        		sql.append(" and c.P00300001='"+depCode+"' \n ");
        		sql.append(" and b.PO0101=a.hosp_icd_code and b.PO0100=a.hosp_icd_desc \n ");
            	sql.append(" ) \n ");
    		}else{
    			sql.append(" and exists( \n ");
            	sql.append(" select 1 from drgs.MR_DIAGNOSE b \n ");
            	sql.append(" left join drgs.MR_IPINFO c \n ");
            	sql.append(" on b.PMAININDEX=c.PMAININDEX \n ");
            	sql.append(" where b.PD0101 is not null and b.PD0100 is not null \n ");
        		sql.append(" and c.P00300001='"+depCode+"' \n ");
        		sql.append(" and b.PD0101=a.hosp_icd_code and b.PD0100=a.hosp_icd_desc \n ");
            	sql.append(" ) \n ");
    		}
    	}
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

	@Override
	public int getBJICDCount(String icdVersion, String icdType) {
		StringBuilder sql = new StringBuilder();
		if("D".equals(icdType)){
			sql.append(" select count(1) from drgs.t_icd_bj_dia t where t.dicd_version='"+icdVersion+"'");
		}else{
			sql.append(" select count(1) from drgs.t_icd_bj_oper t where  t.oicd_version='"+icdVersion+"'");
		} 
		Object result = getRepository().createSqlQuery(sql.toString()).singleResult();
		return result!=null?Integer.parseInt(result.toString()):0;
	}
	
	@Override
	public List<Object[]> getBJICD(String icdType, String icdVersion, String icdValue, String icdValue1) {
		StringBuilder sql = new StringBuilder();
		String tableName="";
		String versionCode="";
		String codeType="";
		String descType="";
		if("O".equals(icdType)){
			tableName="drgs.t_icd_bj_oper";
			versionCode="t.oicd_version";
			codeType="oicd_code";
			descType="oicd_desc";
		}else{
			tableName="drgs.t_icd_bj_dia";
			versionCode="t.dicd_version";
			codeType="dicd_code";
			descType="dicd_desc";
		}
		sql.append(" select "+codeType+", "+codeType+"||' '||"+descType+" from "+tableName+" t where 1=1 \n ");
		if(StringUtils.hasLength(icdVersion)) {
			sql.append(" and "+versionCode+"='"+icdVersion+"' \n ");
		}
		if(StringUtils.hasLength(icdValue1)){
			sql.append(" and ("+codeType+" like '"+icdValue1+"%' or "+descType+" like '%"+icdValue1+"%') \n ");
		}else if(StringUtils.hasLength(icdValue)){
			String icdV = icdValue.substring(0, icdValue.indexOf(".")+1);
			sql.append(" and "+codeType+" like '"+icdV+"%' \n ");
		}
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

	@Override
	public void updateICDMatching(Map<String, Object> json) {
		String tableName = "";
		String hICDCode = json.get("hICDCode") != null ? json.get("hICDCode").toString() : "";
		String newBJICDCode = json.get("bjICDCodeNew") != null ? json.get("bjICDCodeNew").toString() : "";
		String newBJICDDesc = json.get("bjICDDescNew") != null ? json.get("bjICDDescNew").toString() : "";
		String hICDDesc = json.get("hICDDesc")!=null ? json.get("hICDDesc").toString() : "";
		// 处理字符串中含有单引号，造成数据插入或者更新失败的，如：巩膜静脉窦扩张术[Schlemm's管扩张术]
		hICDDesc = hICDDesc.replace("'", "''");
		String currentTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		String icdVersion = json.get("icdVersion") !=null ? json.get("icdVersion").toString() : "";
		json.put("currentTime", currentTime);
		StringBuilder sql = new StringBuilder();
		if ("O".equals(json.get("icdType").toString())) {
			tableName = "drgs.t_icdmatch_result_oper";
		} else {
			tableName = "drgs.t_icdmatch_result_dia";
		}
		sql.append(" update " + tableName + " t set t.bj_icd_code='" + newBJICDCode + "', t.bj_icd_desc=q'/"
				+ newBJICDDesc + "/', icdmat_type='1', last_update_date=to_date('" + currentTime
				+ "','yyyy-mm-dd hh24:mi:ss') \n ");
		sql.append(" where t.hosp_icd_code='" + hICDCode + "' and t.hosp_icd_desc ='" + hICDDesc + "' and t.drg_icd_version='"+icdVersion+"' \n ");
		try {
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
			// 获取本院手术编码版本
			String operIcd = jdbcTemplateObj.queryForObject("SELECT cfg_value FROM drgs.t_config WHERE cfg_code='OPER_VERSION'", String.class);
			String sqlu="";
			if (operIcd.contains("BJ")) {
				sqlu = " update drgs.mr_operation op  set op.PO1400= \n";
				sqlu+= " (SELECT ic.is_oper from drgs.t_icd_bj_oper ic \n";
				sqlu+=" where ic.oicd_code='"+newBJICDCode+"' and ic.oicd_version='"+icdVersion+"' \n";
				sqlu+= ") where op.po0101='"+ hICDCode +"' ";
				getRepository().createSqlQuery(sqlu).executeUpdate();

			} else {
				sqlu =" update drgs.mr_operation a set a.po1400=( \n";
				sqlu+=" select b.is_oper from drgs.t_Icdmatch_Result_Oper b  where b.hosp_icd_code= \n";
				sqlu+=" '"+hICDCode+"' and b.drg_icd_version='"+icdVersion+"') where a.po0101='"+hICDCode+"' \n";
				getRepository().createSqlQuery(sqlu).executeUpdate();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 *  @Description：数据批量导入
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.ICDApplication#importICDMatching(java.lang.String, java.util.Map)
	 */
	@Override
	public Map<String,Integer> importICDMatching(String icdType,Map<String, Object> json) {
		int editType=0;
		int updateCnt=0;
		int insertCnt=0;
		Map<String,Integer>  map=new HashMap<>();
		String hICDCode = json.get("hICDCode")!=null?json.get("hICDCode").toString():"";
		String hICDDesc = json.get("hICDDesc")!=null?json.get("hICDDesc").toString():"";
		//处理字符串中含有单引号，造成数据插入或者更新失败的，如：巩膜静脉窦扩张术[Schlemm's管扩张术]
		hICDDesc = hICDDesc.replace("'", "''");
		String newBJICDCode = json.get("bjICDCode")!=null?json.get("bjICDCode").toString():"";
		String newBJICDDesc = json.get("bjICDDesc")!=null?json.get("bjICDDesc").toString():"";
		//处理字符串中含有单引号，造成数据插入或者更新失败的，如：巩膜静脉窦扩张术[Schlemm's管扩张术]
		newBJICDDesc = newBJICDDesc.replace("'", "''");
		String currentTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		json.put("currentTime", currentTime);
		String icdVersion = json.get("icdVersion")!=null?json.get("icdVersion").toString():"";
		StringBuilder sql = new StringBuilder();
		String tableName="";
		if("D".equals(icdType)){
			tableName="drgs.t_icdmatch_result_dia";
		}else{
			tableName="drgs.t_icdmatch_result_oper"	;
		}
//		//判断该编码是否已经在对照了完全对照
//		sql = new StringBuilder();
//		sql.append(" select t.bj_icd_code from "+tableName+" t where t.hosp_icd_code='"+hICDCode+"' and drg_icd_version='"+icdVersion+"' and t.icdmat_type in('0','1') ");	
//		
//		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString());			
//		//若没有完全对照
//		if(list.isEmpty()) {
			sql = new StringBuilder();
			sql.append(" select bj_icd_code as BJCODE from "+tableName+" t where hosp_icd_code='"+hICDCode+"' and hosp_icd_desc=q'/"+hICDDesc+"/' and drg_icd_version='"+icdVersion+"' ");
			List<Map<String,Object>> listM =jdbcTemplateObj.queryForList(sql.toString());
			if(listM.size()>0){
				String oldBJICDCode = listM.get(0)!=null?(listM.get(0).get("BJCODE")!=null?listM.get(0).get("BJCODE")+"":""):"";
				json.put("bjICDCodeOld", oldBJICDCode);
				sql = new StringBuilder();
				sql.append(" update "+tableName+" t set bj_icd_code='"+newBJICDCode+"', bj_icd_desc=q'/"+newBJICDDesc+"/', icdmat_type='1', last_update_date=to_date('"+currentTime+"','yyyy-mm-dd hh24:mi:ss') \n ");
				sql.append(" where hosp_icd_code='"+hICDCode+"' and hosp_icd_desc =q'/"+hICDDesc+"/' and drg_icd_version='"+icdVersion+"' \n ");
				updateCnt=1;
				editType=2;
			}else{
				sql = new StringBuilder();
				sql.append(" insert into "+tableName+"(hosp_icd_code, hosp_icd_desc, bj_icd_code, bj_icd_desc, icdmat_type, last_update_date, drg_icd_version) values('"+hICDCode+"', q'/"+hICDDesc+"/', '"+newBJICDCode+"', q'/"+newBJICDDesc+"/', '1', to_date('"+currentTime+"','yyyy-mm-dd hh24:mi:ss'), '"+icdVersion+"') \n ");
				insertCnt=1;
				editType=1;
			}
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
//		}
		map.put("insertCnt", insertCnt);
		map.put("updateCnt", updateCnt);
		map.put("editType", editType);
		return map;
	}
	/**
	 * @methodname    :importICDMatchingPlatform
	 * @description   : 数据批量导入(以hosp_icd_code + drg_icd_version为唯一判断条件）对照平台用
	 * @author        :qiaoyilong
	 * @date          :2018年11月16日
	 */
	@Override
	public Map<String,Integer> importICDMatchingPlatform(String icdType,Map<String, Object> json) {
		int editType=0;
		int updateCnt=0;
		int insertCnt=0;
		Map<String,Integer>  map=new HashMap<>();
		String hICDCode = json.get("hICDCode")!=null?json.get("hICDCode").toString():"";
		String hICDDesc = json.get("hICDDesc")!=null?json.get("hICDDesc").toString():"";
		//处理字符串中含有单引号，造成数据插入或者更新失败的，如：巩膜静脉窦扩张术[Schlemm's管扩张术]
		hICDDesc = hICDDesc.replace("'", "''");
		String newBJICDCode = json.get("bjICDCode")!=null?json.get("bjICDCode").toString():"";
		String newBJICDDesc = json.get("bjICDDesc")!=null?json.get("bjICDDesc").toString():"";
		//处理字符串中含有单引号，造成数据插入或者更新失败的，如：巩膜静脉窦扩张术[Schlemm's管扩张术]
		newBJICDDesc = newBJICDDesc.replace("'", "''");
		String currentTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		json.put("currentTime", currentTime);
		String icdVersion = json.get("icdVersion")!=null?json.get("icdVersion").toString():"";
		StringBuilder sql = new StringBuilder();
		String tableName="";
		if("D".equals(icdType)){
			tableName="drgs.t_icdmatch_result_dia";
		}else{
			tableName="drgs.t_icdmatch_result_oper"	;
		}

		sql = new StringBuilder();
		//以hosp_icd_code + drg_icd_version为唯一判断条件
		sql.append(" select bj_icd_code as BJCODE from "+tableName+" t where hosp_icd_code='"+hICDCode+"' and drg_icd_version='"+icdVersion+"' ");
		List<Map<String,Object>> listM =jdbcTemplateObj.queryForList(sql.toString());
		if(listM.size()>0){
			String oldBJICDCode = listM.get(0)!=null?(listM.get(0).get("BJCODE")!=null?listM.get(0).get("BJCODE")+"":""):"";
			json.put("bjICDCodeOld", oldBJICDCode);
			sql = new StringBuilder();
			sql.append(" update "+tableName+" t set bj_icd_code='"+newBJICDCode+"', bj_icd_desc=q'/"+newBJICDDesc+"/', icdmat_type='1', last_update_date=to_date('"+currentTime+"','yyyy-mm-dd hh24:mi:ss') \n ");
			sql.append(" where hosp_icd_code='"+hICDCode+"' and drg_icd_version='"+icdVersion+"' \n ");
			updateCnt=1;
			editType=2;
		}else{
			sql = new StringBuilder();
			sql.append(" insert into "+tableName+"(hosp_icd_code, hosp_icd_desc, bj_icd_code, bj_icd_desc, icdmat_type, last_update_date, drg_icd_version) values('"+hICDCode+"', q'/"+hICDDesc+"/', '"+newBJICDCode+"', q'/"+newBJICDDesc+"/', '1', to_date('"+currentTime+"','yyyy-mm-dd hh24:mi:ss'), '"+icdVersion+"') \n ");
			insertCnt=1;
			editType=1;
		}
		getRepository().createSqlQuery(sql.toString()).executeUpdate();

		map.put("insertCnt", insertCnt);
		map.put("updateCnt", updateCnt);
		map.put("editType", editType);
		return map;
	}
	/**
	 * 
	 *  @Description：更新对照表中全部的手术/操作字段
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.ICDApplication#updateIsOper()
	 */
	@Override
	public void updateIsOper(String icdVersion){
		//插入更新手术/操作字段
		StringBuilder sqlu = new StringBuilder();
		sqlu.append(" update drgs.t_icdmatch_result_oper t set t.is_oper= \n");
		sqlu.append(" (select distinct p.is_oper from drgs.t_icd_bj_oper p where p.oicd_version='"+icdVersion+"' \n");
		sqlu.append(" and t.bj_icd_code=p.oicd_code) where t.drg_icd_version='"+icdVersion+"'");
		try{
			getRepository().createSqlQuery(sqlu.toString()).executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
		return;
	}
	
	@Override
	public void editLog(Map<String, Object> json) {
		String hICDCode = json.get("hICDCode")!=null?json.get("hICDCode").toString():"";
		String userCode = json.get("userCode")!=null?json.get("userCode").toString():"";
		String editType = json.get("editType")!=null?json.get("editType").toString():"";
		String icdType  = json.get("icdType")!=null?json.get("icdType").toString():"";
		String oldBJICDCode = json.get("bjICDCodeOld")!=null?json.get("bjICDCodeOld").toString():"";
		String icdVersion = json.get("icdVersion")!=null?json.get("icdVersion").toString():"";
		String tableName="";
		if(("O").equals(icdType)){
			tableName="drgs.t_icdmatch_result_oper";
		}else{
			tableName="drgs.t_icdmatch_result_dia";
		}
		StringBuilder sql = new StringBuilder();
		sql.append(" insert into drgs.t_icdeditrecord(ICDER_HICD_ID, ICDER_USER, ICDER_DATE, ICDER_TYPE, ICDER_OLD_BJICD, ICDER_NEW_BJICD, ICDER_VERSION) \n ");
		sql.append(" select hosp_icd_code, '"+userCode+"', last_update_date, '"+editType+"', '"+oldBJICDCode+"', bj_icd_code, '"+icdVersion+"' \n ");
		sql.append(" from "+tableName+" t \n ");
		sql.append(" where 1=1 \n ");
		if(StringUtils.hasLength(hICDCode)){
			sql.append(" and hosp_icd_code='"+hICDCode+"' \n ");
		}
		if("1".equals(editType)){
			int updates = json.get("updates")!=null?Integer.parseInt(json.get("updates").toString()):0;
			if(updates>0){
				sql.append(" and last_update_date=(select max(last_update_date) from "+tableName+") \n ");
			}else{
				sql.append(" and 1>2 \n ");
			}
		}
		getRepository().createSqlQuery(sql.toString()).executeUpdate();
	}

	@Override
	public List<Map<String,Object>>  getEditLog(String hICDCode, String icdVersion) {
		String sql = "select ICDER_HICD_ID hICDCode, ICDER_USER userCode, to_char(ICDER_DATE, 'yyyy-mm-dd hh24:mi') editDate, ICDER_TYPE editType, ICDER_OLD_BJICD oldBJICDCode, ICDER_NEW_BJICD newBJICDCode from drgs.T_ICDEDITRECORD where ICDER_HICD_ID='"+hICDCode+"' and ICDER_VERSION='"+icdVersion+"' order by ICDER_DATE desc ";
		List<Map<String,Object>>  resultList=jdbcTemplateObj.queryForList(sql.toString());
    	return resultList;
	}
//icd诊断对照结果汇总
	@Override
	public List<Map<String,Object>> getICDMatchResultTable_D(String isOnlyMatch) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select diag_code icdCode, diagnosis icdDesc, bj_icd_code bjICDCode, bj_icd_desc bjICDDesc, cnt TotalCaseCnt, incnt InCaseCnt, nincnt NotInCaseCnt  from ( \n ");
		sql.append(" select b.PD0101 diag_code, b.PD0100 diagnosis, count(1) cnt, \n ");
		sql.append(" sum(case when c.pardrg_code is not null then 1 else 0 end) incnt, \n ");
		sql.append(" sum(case when c.pardrg_code is null  then 1 else 0 end) nincnt \n ");
		sql.append(" from drgs.MR_IPINFO a \n ");
		sql.append(" left join drgs.t_patientdrg c on a.PMAININDEX=c.parid  \n ");
		sql.append(" left join drgs.MR_DIAGNOSE b on a.PMAININDEX=b.PMAININDEX and b.PDTYPE='M' \n ");
		sql.append(" where a.P00320000<61 \n ");
		sql.append(" group by b.PD0101, b.PD0100 \n ");
		sql.append(" ) w left join ( \n ");
		sql.append(" select t.hosp_icd_code, t.hosp_icd_desc, t.bj_icd_code, t.bj_icd_desc,  \n ");
		sql.append(" row_number() over(partition by t.hosp_icd_code order by t.hosp_icd_desc) n from drgs.t_icdmatch_result_dia t \n");
		sql.append(" where icdmat_type='1' or icdmat_type='0' \n ");
		sql.append(" ) x \n ");
		sql.append(" on w.diag_code=x.hosp_icd_code and x.n=1 \n ");
		sql.append(" where 1=1 \n ");
		if("1".equals(isOnlyMatch)){
			sql.append(" and bj_icd_code is null \n ");
		}
		List<Map<String,Object>>  resultList=jdbcTemplateObj.queryForList(sql.toString());
    	return resultList;
	}
	//icd手术对照结果汇总
	@Override
	public List<Map<String,Object>>  getICDMatchResultTable_O(String isOnlyMatch) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select oper_code icdCode, operation icdDesc, bj_icd_code bjICDCode, bj_icd_desc bjICDDesc, cnt TotalCaseCnt, incnt InCaseCnt, nincnt NotInCaseCnt from (  \n ");
		sql.append(" select b.PO0101 oper_code, b.PO0100 operation, count(1) cnt, \n ");
		sql.append(" sum(case when c.pardrg_code is not null then 1 else 0 end) incnt, \n ");
		sql.append(" sum(case when c.pardrg_code is null then 1 else 0 end) nincnt \n ");
		sql.append(" from drgs.MR_IPINFO a \n ");
		sql.append(" left join drgs.t_patientdrg c on a.PMAININDEX=c.parid  \n ");
		sql.append(" left join drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX and b.POTYPE='M' \n ");
		sql.append(" where a.P00320000<61 \n ");
		sql.append(" and b.PO0101 is not null \n ");
		sql.append(" group by b.PO0101, b.PO0100 \n ");
		sql.append(" ) w left join ( \n ");
		sql.append(" select t.hosp_icd_code, t.hosp_icd_desc, t.bj_icd_code, t.bj_icd_desc,  \n ");
		sql.append(" row_number() over(partition by t.hosp_icd_code order by t.hosp_icd_desc) n from drgs.t_icdmatch_result_oper t \n");
		sql.append(" where icdmat_type='1' or icdmat_type='0' \n ");
		sql.append(" ) x \n ");
		sql.append(" on w.oper_code=x.hosp_icd_code and x.n=1 \n ");
		sql.append(" where 1=1 \n ");
		if("1".equals(isOnlyMatch)){
			sql.append(" and bj_icd_code is null \n ");
		}
		List<Map<String,Object>>  resultList=jdbcTemplateObj.queryForList(sql.toString());
    	return resultList;
	}
	/**
	 * 
	 *  @Description：根据编码获取描述
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.ICDApplication#findDescByCode(java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String,Object>>  findDescByCode(String code, String type) {
		String sql="";
		if("D".equals(type)){
			 sql="select a.pd0100 name,count(a.id) cnt from  drgs.mr_diagnose a  where a.pd0101='"+code+"' and a.pd0100 is not null group by a.pd0100  order by count(a.id) desc";
		}else{
			 sql="select a.po0100 name ,count(a.id) cnt from  drgs.mr_operation a  where a.po0101='"+code+"' and a.po0100 is not null  group by a.po0100 order by count(a.id) desc ";
		}
		List<Map<String,Object>>  resultList=jdbcTemplateObj.queryForList(sql.toString());
    	return resultList;
	}
	/**
	 * 
	 *  @Description：根据BJ编码获取描述
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.ICDApplication#findBJDescByCode(java.lang.String, java.lang.String)
	 */
	@Override
	public Object findBJDescByCode(String code, String type, String icdVersion) {
		String sql = "";
		if ("O".equals(type)) {
			sql = " select b.oicd_desc name from drgs.t_icd_bj_oper b WHERE b.oicd_code='"+code+"' and b.oicd_version='"+icdVersion+"' ";
		} else {
			sql = " select b.dicd_desc name from drgs.t_icd_bj_dia b WHERE b.dicd_code='"+code+"' and b.dicd_version='"+icdVersion+"' ";
		}
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
		}
	/**
	 * 
	 *  @Description：ICD对照批量处理
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.ICDApplication#batchSummitIcd(java.lang.String, java.util.Map)
	 */
	@Override
	public void batchSummitIcd(String icdType, String matchType, String icdVersion) {
		String currentTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		String tableName= "";
		if("O".equals(icdType)){
			tableName="DRGS.T_ICDMATCH_RESULT_OPER";
		}else{
			tableName="DRGS.T_ICDMATCH_RESULT_DIA";
		}
		StringBuilder sql = new StringBuilder();
		sql.append(" update "+tableName+" t set icdmat_type='1', last_update_date=to_date('"+currentTime+"','yyyy-mm-dd hh24:mi:ss') \n ");
		sql.append(" where drg_icd_version='"+icdVersion+"' and icdmat_type='"+matchType+"' \n ");
		getRepository().createSqlQuery(sql.toString()).executeUpdate();
	}
/**
 * 
 *  @Description：获取北京icd
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.ICDApplication#findBjIcdCode(java.lang.String, java.lang.String)
 */
	@Override
	public Object findBjIcdCode(String icdVersion, String icdType) {
		String sql="";
		if("O".equals(icdType)){
			 sql+=" SELECT b.oicd_code  ICDCODE,b.oicd_desc ICDDESC FROM drgs.t_icd_bj_oper b WHERE  b.oicd_version='"+icdVersion+"'";
		}else{
			 sql+=" SELECT b.dicd_code  ICDCODE,b.dicd_desc ICDDESC FROM drgs.t_icd_bj_dia b WHERE  b.dicd_version='"+icdVersion+"'";
		}
		return jdbcTemplateObj.queryForList(sql);
	}

/**
 * 
 *  @Description：更新单条手术icd的手术/操作字段
 *  @Author ZouYiyu  
 *  @see com.dhcc.DRGsHosp.application.ICDApplication#updateIsOperOne(java.util.Map)
 */
@Override
	public void updateIsOperOne(Map<String, Object> paramMap) {
		String sql = " update drgs.t_icdmatch_result_oper t set t.is_oper= \n";
		sql += " ( select p.is_oper from drgs.t_icd_bj_oper p \n";
		sql += " where  p.oicd_version='"+paramMap.get("icdVersion")+"' and (p.oicd_code='" + paramMap.get("bjICDCode") + "' or p.oicd_desc=q'/"
				+ paramMap.get("bjICDDesc") + "/')  \n";
		sql += " ) where t.bj_icd_code='" + paramMap.get("bjICDCode") + "' \n";
		getRepository().createSqlQuery(sql.toString()).executeUpdate();
		// 获取本院手术编码版本
		String operIcd = jdbcTemplateObj.queryForObject("SELECT cfg_value FROM drgs.t_config WHERE cfg_code='OPER_VERSION'", String.class);
		if (operIcd.contains("BJ")) {
			sql = " update drgs.mr_operation op  set op.PO1400= \r\n"
					+ " (SELECT ic.is_oper from drgs.t_icd_bj_oper ic \r\n"
					+ " where ic.oicd_code='"+paramMap.get("bjICDCode")+"'  and ic.oicd_version='" + paramMap.get("icdVersion") + "') \r\n"
					+ " where op.po0101='"+paramMap.get("hICDCode")+"' ";
			getRepository().createSqlQuery(sql).executeUpdate();

		} else {
			sql = " update drgs.mr_operation a set a.po1400=( \r\n"
					+ " select b.is_oper from drgs.t_Icdmatch_Result_Oper b  where b.hosp_icd_code='"+paramMap.get("hICDCode")+"' \r\n" 
					+ "  ) where a.po0101='"+paramMap.get("hICDCode")+"' \r\n";
			getRepository().createSqlQuery(sql).executeUpdate();
		}
	}
/**
 * 
 *  @Description：调用存储过程更新手术表中的手术/操作字段
 *  @Author ZouYiyu  
 *  @see com.dhcc.DRGsHosp.application.ICDApplication#updateMrIsOper(java.lang.String, java.lang.String)
 */
@Override
public void updateMrIsOper(String pMainIndex,String operIcd){
	Connection conn= null;
	try{
		conn=jdbcTemplateObj.getDataSource().getConnection();
		conn.setAutoCommit(true);
		CallableStatement statement=conn.prepareCall("call DRGS.PROC_OPERATION_ISOPER(?,?)");
		statement.setString(1, pMainIndex);
		statement.setString(2, operIcd);
		statement.execute();
	}catch(SQLException e){
		e.printStackTrace();
	}finally{
		try{
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
}
	/**
	 * 
	 *  @Description：获取手术icd版本
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.ICDApplication#getOperIcd()
	 */
	@Override
	public String getOperIcd() {
		String operIcd = "";
		// 获取本院手术编码版本
		String sql = " SELECT cfg_value FROM drgs.t_config WHERE cfg_code='OPER_VERSION' ";
		operIcd = jdbcTemplateObj.queryForObject(sql, String.class);
		return operIcd;
	}
/**
 * 
 *  @Description：根据出院日期查询主键（有手术）
 *  @Author ZouYiyu  
 *  @see com.dhcc.DRGsHosp.application.ICDApplication#findIdByOutDate(java.lang.String, java.lang.String)
 */
@Override
public List<Map<String,Object>> findIdByOutDate(String dateFrom,String dateTo){
	String sql = "select t.pmainindex id from drgs.mr_ipinfo t left join drgs.mr_operation p on t.pmainindex=p.pmainindex \n";
	sql+=" where to_char(P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' and p.pmainindex is not null";
	return jdbcTemplateObj.queryForList(sql);
}

	/**
	 * 
	 * @Description：更新手术表中的is_oper字段
	 * @Author ZouYiyu
	 * @see com.dhcc.DRGsHosp.application.ICDApplication#updateOperation(java.lang.String)
	 */
	@Override
	public void updateOperation(String icdVersion) {
		String sql = "";
		// 获取本院手术编码版本
		String operIcd = jdbcTemplateObj.queryForObject("SELECT cfg_value FROM drgs.t_config WHERE cfg_code='OPER_VERSION'", String.class);
		if (operIcd.contains("BJ")) {
			sql = " update drgs.mr_operation op  set op.PO1400=\r\n"
					+ " (SELECT ic.is_oper from drgs.t_icd_bj_oper ic \r\n"
					+ "   where op.po0101=ic.OICD_CODE  and ic.oicd_version='" + icdVersion + "')";
			getRepository().createSqlQuery(sql).executeUpdate();

		} else {
			sql = " update drgs.mr_operation a set a.po1400=(\r\n"
					+ " select is_oper from drgs.t_Icdmatch_Result_Oper b\r\n" + "  where a.po0101=b.hosp_icd_code \r\n"
					+ " and b.icdmat_type not in ('3','4') )";
			getRepository().createSqlQuery(sql).executeUpdate();
		}

	}
	/**
	 * 
	 *  @Description：查询数据库中icd字典版本
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.ICDApplication#operDicType(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> operDicType(String icdType) {
		String sql="";
		if("O".equals(icdType)){
			sql=" select a.dic_value,a.dic_desc from drgs.mr_dictionarydata a  \n";
			sql+= " where a.dic_code='OPER_ICD_VERSION' \n";
			sql+=" and a.dic_value in( select distinct oicd_version  from drgs.t_icd_bj_oper)";
		}else{
			sql=" select a.dic_value,a.dic_desc from drgs.mr_dictionarydata a \n";
			sql+=" where a.dic_code='DIA_ICD_VERSION' \n";
			sql+=" and a.dic_value in (select distinct dicd_version from drgs.t_icd_bj_dia) ";
		}
		return jdbcTemplateObj.queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> getNoCompareCase(String icdCode,String icdDesc, String icdType) {
		String sql="";
		sql=" select mr.pmainindex as id,BS.P00050000 mrNo,bs.P00060000 as patientName,BS.P00070001 as sex,BS.P00090000 as ageYear, \n";
		sql+=" to_char(mr.P00250000,'yyyy-mm-dd') as inDate,to_char(mr.P00290000,'yyyy-mm-dd') as outDate, \n";
		sql+=" mr.P00300000 as outDep,mr.P00320000 as inDays,mr.P00800000 as costamount, \n";
		sql+=" diag.PD0101 as diagnoseCode,diag.PD0100 as diagnoseDessmain,diaginfo.oth_diaginfo_code ,diaginfo.oth_diaginfo_desc , \n";
		sql+=" operinfo.operinfo_code ,operinfo.operinfo_desc from drgs.MR_IPINFO mr \n";
		sql+=" LEFT JOIN drgs.mr_baseinfo BS ON BS.PMAININDEX=MR.PMAININDEX \n";
		sql+=" LEFT JOIN drgs.MR_DIAGNOSE diag ON mr.PMAININDEX=diag.PMAININDEX AND diag.PDTYPE='M' \n";
		if("D".equals(icdType)){
			sql+=" LEFT JOIN drgs.MR_DIAGNOSE dia ON mr.PMAININDEX=dia.PMAININDEX  \n";
		}else{
			sql+=" LEFT JOIN drgs.mr_operation ope ON mr.PMAININDEX=ope.PMAININDEX  \n";
		}
		sql+=" LEFT JOIN (SELECT DIAG.PMAININDEX, LISTAGG(DIAG.PD0100,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  oth_diaginfo_desc, \n";
		sql+=" LISTAGG(DIAG.PD0101,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  oth_diaginfo_code \n";
		sql+=" FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP  ON DIAG.PMAININDEX = IP.PMAININDEX \n";
		sql+=" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX where diag.pdtype='O' \n";
		sql+=" GROUP BY DIAG.PMAININDEX) DIAGINFO ON MR.PMAININDEX = DIAGINFO.PMAININDEX \n";
		sql+=" LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG(OPER.PO0101,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  operinfo_code, \n";
		sql+=" LISTAGG(OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  operinfo_desc \n";
		sql+=" FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n";
		sql+=" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n";
		sql+=" GROUP BY OPER.PMAININDEX) OPERINFO ON MR.PMAININDEX = OPERINFO.PMAININDEX \n";
		sql+=" left join drgs.t_department dep on dep.dep_code =mr.P00300001 \n";
		if("D".equals(icdType)){
			sql+=" where dia.pd0101='"+icdCode+"' and dia.pd0100='"+icdDesc+"' ";
		}else{
			sql+=" where ope.po0101='"+icdCode+"' and ope.po0100='"+icdDesc+"' ";
		}
		return jdbcTemplateObj.queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> findDiags(String condition) {
		String sql = "SELECT DICD_CODE \"diagCode\", DICD_DESC \"diagDesc\"" +
				"        FROM DRGS.T_ICD_BJ_DIA" +
				"        WHERE " +
				"            DICD_CODE LIKE '%"+condition+"%'" +
				"            OR " +
				"            DICD_DESC LIKE '%"+condition+"%'";
		return jdbcTemplateObj.queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> findOpers(String condition) {
		String sql = "SELECT OICD_CODE \"operCode\", OICD_DESC \"operDesc\"" +
				"        FROM DRGS.T_ICD_BJ_OPER" +
				"        WHERE" +
				"            OICD_CODE LIKE '%"+condition+"%'" +
				"            OR" +
				"            OICD_DESC LIKE '%"+condition+"%'";
		return jdbcTemplateObj.queryForList(sql);
	}

}
